package id.net.iconpln.bb.server.dao;

import id.net.iconpln.bb.server.util.CommonModule;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import oracle.jdbc.OracleTypes;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class PortDao {
	@Autowired	private SessionFactory sessionFactory;
	
	public List<Map> getListDermagaBy(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;            
            if (kode.equals("KEDATANGAN")){

                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); //session login user
                call.execute();
                
                System.out.println("out = " + sql + "kode :" + kode + "id pltu " + map.get("id_pltu").toString());
            }else if (kode.equals("BONGKAR")){
                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); //session login user
                call.execute();
                
                System.out.println("out = " + sql + "kode :" + kode + " pltu " + map.get("id_pltu").toString());
            }else if (kode.equals("SANDAR")){
                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); //session login user
//                call.setString(1, "2"); //session login user
                call.execute();            	
                System.out.println("out = " + sql + "kode :" + kode);
            }else if (kode.equals("REC_BONGKAR")){
	
            	sql = "{call BBTRXDERMAGA.SETID_BONGKAR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_bongkar").toString());
                call.execute();
                
                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); //session login user
                call.execute();
                
                System.out.println("out = " + sql + "kode :" + kode + " id_bongkar " + map.get("id_bongkar").toString() + " pltu " + map.get("id_pltu").toString());
            }else if (kode.equals("BANDINGSAMPLING")){
                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString()); //session login user
                call.execute();
            }   	
            
            
            sql = "{? = call BBTRXDERMAGA.GETLISTDERMAGABY(?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.CURSOR);
            call.setString(2, kode);
            call.execute();
            
            
            
            ResultSet rset = (ResultSet) call.getObject(1);
            lMap = CommonModule.convertResultsetToListLower(rset);
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  

		}
        
        return lMap;
	}
	
	public List<Map> insertDermagaBb(String kode, Map map){
		List<Map> lMap = new ArrayList<Map>();
		Session session = sessionFactory.getCurrentSession();
        Connection con = session.connection();
        try {
            String sql = null;
            CallableStatement call;
            if (kode.equals("KEDATANGAN")){
            	
            	sql = "{call BBTRXDERMAGA.SETID_PENGIRIMAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pengiriman").toString());
                call.execute();
                
                sql = "{call BBTRXDERMAGA.SETID_UNIT_JETTY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_unit_jetty").toString());
//                call.setInt(1, Integer.parseInt(map.get("id_unit_jetty").toString().trim()));
                call.execute();
                

                sql = "{call BBTRXDERMAGA.SETNO_PENGIRIMAN(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_pengiriman").toString());
                call.execute();
                

                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_pltu").toString());
                call.execute();
                

                sql = "{call BBTRXDERMAGA.SETNO_SHIPMENT(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_pengiriman").toString());
                call.execute();
                

                sql = "{call BBTRXDERMAGA.SETNO_RAKOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_rakor").toString());
                call.execute();
                
                sql = "{call BBTRXDERMAGA.SETTIME(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("time").toString());
                call.execute();
                
                sql = "{call BBTRXDERMAGA.SETTGL_TD(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("tgl_td").toString());
                call.execute();
                

                sql = "{call BBTRXDERMAGA.SETPELAPOR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("pelapor").toString());
                call.execute();
                

                sql = "{call BBTRXDERMAGA.SETCREATEBY(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("createby").toString());
                call.execute();
                
                
                sql = "{call BBTRXDERMAGA.SETAPP_ACCESS(?)}";
                call = con.prepareCall(sql);
                call.setString(1, "WEB");
                call.execute();
            }else if (kode.equals("BONGKAR")){
            		if (map.get("crud").toString().equals("UPDATE")){
            			
                        String str_date= map.get("time_bongkar").toString();
		                String str_datet= map.get("tanggal").toString();
		                
		                DateFormat formatter,formatter2 ; 
		                Date date,datet ; 
		                 formatter = new SimpleDateFormat("dd/MM/yyyy");
		                 formatter2 = new SimpleDateFormat("dd/MM/yyyy hh.mm");
		                  date = (Date)formatter.parse(str_date); 
		                  datet = (Date)formatter.parse(str_datet); 
		
		                sql = "{call BBTRXDERMAGA.SETTANGGAL(?)}";
		                call = con.prepareCall(sql);
		//                call.setString(1, map.get("tanggal").toString());
		                call.setDate(1, new java.sql.Date(date.getTime()));
		                call.execute();
		                     
		            	sql = "{call BBTRXDERMAGA.SETTIME_BONGKAR(?)}";
		                call = con.prepareCall(sql);
		                call.setDate(1, new java.sql.Date(date.getTime()));
		                call.execute();
		                
		                sql = "{call BBTRXDERMAGA.SETUPDATEBY(?)}";
		                call = con.prepareCall(sql);
		                call.setString(1, map.get("updateby").toString());
		                call.execute();
		                
		                sql = "{call BBTRXDERMAGA.SETALAT_BONGKAR(?)}";
		                call = con.prepareCall(sql);
		                call.setString(1, map.get("alat_bongkar").toString());
		                call.execute();
		                
		                sql = "{call BBTRXDERMAGA.SETNO_BONGKAR(?)}";
		                call = con.prepareCall(sql);
		                call.setString(1, map.get("no_bongkar").toString());
		                call.execute();
		                
		                
            		}else{
                
				
				                sql = "{call BBTRXDERMAGA.SETID_TRANSPORTIR(?)}";
				                call = con.prepareCall(sql);
				                call.setInt(1, Integer.parseInt(map.get("id_transportir").toString().trim()));
				//                call.setString(1, map.get("id_transportir").toString());
				                call.execute();
				
				                String str_date= map.get("time_merapat").toString();
				                String str_datet= map.get("tanggal").toString();
				                DateFormat formatter,formatter2 ; 
				                Date date,datet ; 
				                 formatter = new SimpleDateFormat("dd/MM/yyyy");
				                 formatter2 = new SimpleDateFormat("dd/MM/yyyy hh.mm");
				                  date = (Date)formatter.parse(str_date); 
				                  datet = (Date)formatter.parse(str_datet); 
				
				                sql = "{call BBTRXDERMAGA.SETTANGGAL(?)}";
				                call = con.prepareCall(sql);
				//                call.setString(1, map.get("tanggal").toString());
				                call.setDate(1, new java.sql.Date(date.getTime()));
				                call.execute();
				                
				//                ate date = format.parse(dateString);
				//                SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); //("yyyy-MM-dd-hh.mm.ss");
				                     
				            	sql = "{call BBTRXDERMAGA.SETTIME_MERAPAT(?)}";
				                call = con.prepareCall(sql);
				                call.setDate(1, new java.sql.Date(date.getTime()));
				                call.execute();
				
				                
				            	sql = "{call BBTRXDERMAGA.SETID_KEDATANGAN(?)}";
				                call = con.prepareCall(sql);
				//                call.setString(1, map.get("id_kedatangan").toString());
				                call.setInt(1, Integer.parseInt(map.get("id_kedatangan").toString().trim()));
				                call.execute();
				                
				            	sql = "{call BBTRXDERMAGA.SETID_PENGIRIMAN(?)}";
				                call = con.prepareCall(sql);
				//                call.setString(1, map.get("id_pengiriman").toString());
				                call.setInt(1, Integer.parseInt(map.get("id_pengiriman").toString().trim()));
				                call.execute();
				                
				                sql = "{call BBTRXDERMAGA.SETID_UNIT_JETTY(?)}";
				                call = con.prepareCall(sql);
				//                call.setString(1, map.get("id_unit_jetty").toString());
				                call.setInt(1, Integer.parseInt(map.get("id_unit_jetty").toString().trim()));
				                call.execute();
				                
				
				                sql = "{call BBTRXDERMAGA.SETNO_PENGIRIMAN(?)}";
				                call = con.prepareCall(sql);
				                call.setString(1, map.get("no_pengiriman").toString());
				                call.execute();
				                
				

				                
				
				                sql = "{call BBTRXDERMAGA.SETNO_SHIPMENT(?)}";
				                call = con.prepareCall(sql);
				                call.setString(1, map.get("no_pengiriman").toString());
				                call.execute();
				                
				
				                sql = "{call BBTRXDERMAGA.SETNO_RAKOR(?)}";
				                call = con.prepareCall(sql);
				                call.setString(1, map.get("no_rakor").toString());
				                call.execute();
				                
				                sql = "{call BBTRXDERMAGA.SETCREATEBY(?)}";
				                call = con.prepareCall(sql);
				                call.setString(1, map.get("createby").toString());
				                call.execute();
            		}
            }else if (kode.equals("REC_BONGKAR")){

                
    			sql = "{call BBTRXDERMAGA.SETID_BONGKAR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("id_bongkar").toString());
                call.execute();

    			
                sql = "{call BBTRXDERMAGA.SETNO_BONGKAR(?)}";
                call = con.prepareCall(sql);
                call.setString(1, map.get("no_bongkar").toString());
                call.execute();
                

						
		                String str_date= map.get("record_time").toString();
		                String str_datet= map.get("tanggal").toString();
		                DateFormat formatter,formatter2 ; 
		                Date date,datet ; 
		                 formatter = new SimpleDateFormat("dd/MM/yyyy hh:mm");
		                 formatter2 = new SimpleDateFormat("dd/MM/yyyy");
		                  date = (Date)formatter.parse(str_date); 
		                  datet = (Date)formatter2.parse(str_datet); 
		
		                sql = "{call BBTRXDERMAGA.SETRECORD_TIME(?)}";
		                call = con.prepareCall(sql);
		//                call.setString(1, map.get("tanggal").toString());
		                call.setDate(1, new java.sql.Date(date.getTime()));
		                call.execute();
		                
		//                ate date = format.parse(dateString);
		//                SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy"); //("yyyy-MM-dd-hh.mm.ss");
		                     
		            	sql = "{call BBTRXDERMAGA.SETTANGGAL(?)}";
		                call = con.prepareCall(sql);
		                call.setDate(1, new java.sql.Date(date.getTime()));
		                call.execute();
		
		                
		                sql = "{call BBTRXDERMAGA.SETCREATEBY(?)}";
		                call = con.prepareCall(sql);
		                call.setString(1, map.get("createby").toString());
		                call.execute();
		                
		                sql = "{call BBTRXDERMAGA.SETVOLUME(?)}";
		                call = con.prepareCall(sql);
		                call.setString(1, map.get("volume").toString());
		                call.execute();
		                
		                sql = "{call BBTRXDERMAGA.SETKETERANGAN(?)}";
		                call = con.prepareCall(sql);
		                call.setString(1, map.get("keterangan").toString());
		                call.execute();
		                
    		}
        
	                sql = "{call BBTRXDERMAGA.SETID_PLTU(?)}";
	                call = con.prepareCall(sql);
	                call.setString(1, map.get("id_pltu").toString());
	                call.execute();	 	
            		
	                sql = "{call BBTRXDERMAGA.SETAPP_ACCESS(?)}";
	                call = con.prepareCall(sql);
	                call.setString(1, map.get("app_access").toString());
	                call.execute();
	            
            
            sql = "{? = call BBTRXDERMAGA.INSERTDERMAGABB(?,?)}";
            call = con.prepareCall(sql);
            call.registerOutParameter(1, OracleTypes.VARCHAR);
            call.setString(2, kode);
            call.setString(3, map.get("crud").toString());            
            call.execute();
            
            map.clear();
            map.put("message", call.getString(1));
        }catch (Exception e) {
			e.printStackTrace();
			map.clear();
            map.put("message", e.getMessage());  
            
		}
        lMap.add(map);
        return lMap;

	}
}
